TRUNC[ATE] (datetime)

Purpose

This function returns a date and/or a timestamp, which is trimmed following the format definition. Accordingly, TRUNC(datetime) behaves in the same way as ROUND (datetime), with the exception that TRUNC rounds-down.

Syntax

trunc (datetime)::=

Usage Notes

  • The following elements can be used as format:

    • CC, SCC Century
    YYYY, SYYY, YEAR, SYEAR, YYY, YY, Y Year
    IYYY, IYY, IY, I Year in accordance with international standard, ISO 8601
    Q Quarter
    MONTH, MON, MM, RM Month
    WW Same day of the week as the first day of the year
    IW Same day of the week as the first day of the ISO year
    W Same day of the week as the first day of the month
    DDD, DD, J Day
    DAY, DY, D Starting day of the week. The first day of a week is defined by the parameter NLS_FIRST_DAY_OF_WEEK (refer to ALTER SESSION and ALTER SYSTEM).
    HH, HH24, HH12 Hour
    MI Minute
    SS Seconds

  • A similar functionality provides the PostgreSQL compatible function DATE_TRUNC.
  • If a format is not specified, the value is truncated to days.
  • For data type TIMESTAMP WITH LOCAL TIME ZONE this function is calculated within the session time zone.

Example

SELECT TRUNC(DATE '2006-12-31', 'MM') TRUNC;

SELECT TRUNC(TIMESTAMP '2006-12-31 23:59:59', 'MI') TRUNC;